import express from "express";
import mysql from "mysql";

const app = express();
app.use(express.json()); // 解析JSON请求体，用解析后的请求体填充 req.body 属性

const connection = mysql.createConnection({
  host: "localhost",
  port: "3306",
  user: "root",
  password: "123456",
  database: "personnel_system",
});

const base = "/vite-vue3";
// connection.connect((err) => {
//   if (err) {
//     return console.error(err.message);
//   }
//   console.log("与 MySQL 建立连接");
// });

app.post(`${base}/personnelList`, (req, res) => {
  const params = req.body;
  const pageSize = params.pageSize;
  const startIndex = (params.currentPage - 1) * pageSize;

  let conditions = [];
  let orConds = [];
  for (const key in params) {
    if (key === "currentPage" || key === "pageSize") continue;

    if (params[key]) {
      if (Array.isArray(params[key])) {
        if (params[key].length > 0) {
          orConds = [];
          params[key].forEach((item) => {
            orConds.push(`${key} = '${item}'`);
          });
          conditions.push(`(${orConds.join(" or ")})`);
        }
      } else {
        conditions.push(`${key} = '${params[key]}'`);
      }
    }
  }
  if (conditions.length > 0) {
    conditions = `WHERE ${conditions.join(" AND ")}`;
  }

  const queryList = new Promise((resolve, inject) => {
    const sql = `SELECT * FROM personnel_info
      ${conditions} ORDER BY birthDate DESC
      LIMIT ${startIndex},${pageSize}`;

    connection.query(sql, (error, result) => {
      resolve(result);
    });
  });

  const totalQuery = new Promise((resolve, inject) => {
    const sql = "SELECT COUNT(id) FROM personnel_info";
    connection.query(sql, (error, result) => {
      resolve(result);
    });
  });

  Promise.all([queryList, totalQuery])
    .then(([list, total]) => {
      res.send({
        message: "success",
        status: 200,
        data: {
          list,
          total: total[0]["COUNT(id)"],
        },
      });
    })
    .catch(() => {
      //
    });
});

app.post(`${base}/addPersonnel`, (req, res) => {
  const params = req.body;

  const columns = `(${Object.keys(params).join()})`;
  let values = "";
  Object.values(params).forEach((item) => {
    if (item) {
      if (typeof item === "number") {
        values += `${item},`;
      } else {
        values += `'${item}',`;
      }
    } else {
      values += `null,`;
    }
  });
  values = `(${values.slice(0, -1)})`;

  const sql = `INSERT INTO personnel_info ${columns} VALUES ${values}`;
  connection.query(sql, (error, result) => {
    res.send({
      message: "success",
      status: 200,
    });
  });
});

app.put(`${base}/updatePersonnel`, (req, res) => {
  const params = req.body;
  const values = [];
  for (const key in params) {
    if (key === "id") continue;

    if (params[key]) {
      values.push(`${key}='${params[key]}'`);
    } else {
      values.push(`${key}=null`);
    }
  }

  const sql = `UPDATE personnel_info SET ${values.join()} 
  WHERE id='${params.id}'`;

  connection.query(sql, (error, result) => {
    res.send({
      message: "success",
      status: 200,
    });
  });
});

app.delete(`${base}/deletePersonnel`, (req, res) => {
  const sql = `DELETE FROM personnel_info WHERE id=${req.query.id}`;
  connection.query(sql, (error, result) => {
    res.send({
      message: "success",
      status: 200,
    });
  });
});

// connection.end((err) => {
//   if (err) {
//     return console.log(err);
//   }
//   console.log("connection close");
// });

// 启动服务
app.listen(9000, () => {
  console.log("服务已经启动");
});
